using System;
using System.Data;
using System.Data.OleDb;
using PCSComUtils.Common;
using PCSComUtils.DataAccess;

using PCSComUtils.PCSExc;

namespace PCSComPayroll.DS
{
	public class PAY_PayrollParamDS //: IObjectDS
	{
		public PAY_PayrollParamDS()
		{
		}

		private const string THIS = "PCSComPayroll.DS.DS.PAY_PayrollParamDS";


		//**************************************************************************              
		///    <Description>
		///       This method uses to add data to PAY_PayrollParam
		///    </Description>
		///    <Inputs>
		///        PAY_PayrollParamVO       
		///    </Inputs>
		///    <Outputs>
		///       newly inserted primarkey value
		///    </Outputs>
		///    <Returns>
		///       void
		///    </Returns>
		///    <Authors>
		///       Code generate
		///    </Authors>
		///    <History>
		///       Friday, January 12, 2007
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Add(object pobjObjectVO)
		{
			const string METHOD_NAME = THIS + ".Add()";

			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				PAY_PayrollParamVO objObject = (PAY_PayrollParamVO) pobjObjectVO;
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand("", oconPCS);

				strSql = "INSERT INTO PAY_PayrollParam("
					+ PAY_PayrollParamTable.FROMDATE_FLD + ","
					+ PAY_PayrollParamTable.TODATE_FLD + ","
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD + ","
					+ PAY_PayrollParamTable.STATUS_FLD + ")"
					+ "VALUES(?,?,?,?)";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.FROMDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PAY_PayrollParamTable.FROMDATE_FLD].Value = objObject.FromDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.TODATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PAY_PayrollParamTable.TODATE_FLD].Value = objObject.ToDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.PAIDPERHOUR_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_PayrollParamTable.PAIDPERHOUR_FLD].Value = objObject.PaidPerHour;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.STATUS_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[PAY_PayrollParamTable.STATUS_FLD].Value = objObject.Status;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to delete data from PAY_PayrollParam
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       void
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       Code generate
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Delete(int pintID)
		{
			const string METHOD_NAME = THIS + ".Delete()";
			string strSql = String.Empty;
			strSql = "DELETE " + PAY_PayrollParamTable.TABLE_NAME + " WHERE  " + "PayrollParamID" + "=" + pintID.ToString();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
				ocmdPCS = null;

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}


			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to get data from PAY_PayrollParam
		///    </Description>
		///    <Inputs>
		///        ID       
		///    </Inputs>
		///    <Outputs>
		///       PAY_PayrollParamVO
		///    </Outputs>
		///    <Returns>
		///       PAY_PayrollParamVO
		///    </Returns>
		///    <Authors>
		///       Code Generate 
		///    </Authors>
		///    <History>
		///       Friday, January 12, 2007
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public object GetObjectVO(int pintID)
		{
			const string METHOD_NAME = THIS + ".GetObjectVO()";
			DataSet dstPCS = new DataSet();

			OleDbDataReader odrPCS = null;
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				strSql = "SELECT "
					+ PAY_PayrollParamTable.PAYROLLPARAMID_FLD + ","
					+ PAY_PayrollParamTable.FROMDATE_FLD + ","
					+ PAY_PayrollParamTable.TODATE_FLD + ","
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD + ","
					+ PAY_PayrollParamTable.STATUS_FLD
					+ " FROM " + PAY_PayrollParamTable.TABLE_NAME
					+ " WHERE " + PAY_PayrollParamTable.PAYROLLPARAMID_FLD + "=" + pintID;

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				odrPCS = ocmdPCS.ExecuteReader();

				PAY_PayrollParamVO objObject = new PAY_PayrollParamVO();

				while (odrPCS.Read())
				{
					objObject.PayrollParamID = int.Parse(odrPCS[PAY_PayrollParamTable.PAYROLLPARAMID_FLD].ToString());
					try
					{
						objObject.FromDate = DateTime.Parse(odrPCS[PAY_PayrollParamTable.FROMDATE_FLD].ToString());
					}
					catch{}
					try
					{
						objObject.ToDate = DateTime.Parse(odrPCS[PAY_PayrollParamTable.TODATE_FLD].ToString());
					}
					catch{}
					try
					{
						objObject.PaidPerHour = Decimal.Parse(odrPCS[PAY_PayrollParamTable.PAIDPERHOUR_FLD].ToString());
					}
					catch{}
					try
					{
						objObject.Status = bool.Parse(odrPCS[PAY_PayrollParamTable.STATUS_FLD].ToString());
					}
					catch{}
				}
				return objObject;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update data to PAY_PayrollParam
		///    </Description>
		///    <Inputs>
		///       PAY_PayrollParamVO       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       Code Generate 
		///    </Authors>
		///    <History>
		///       09-Dec-2004
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void Update(object pobjObjecVO)
		{
			const string METHOD_NAME = THIS + ".Update()";

			PAY_PayrollParamVO objObject = (PAY_PayrollParamVO) pobjObjecVO;


			//prepare value for parameters
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				strSql = "UPDATE PAY_PayrollParam SET "
					+ PAY_PayrollParamTable.FROMDATE_FLD + "=   ?" + ","
					+ PAY_PayrollParamTable.TODATE_FLD + "=   ?" + ","
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD + "=   ?" + ","
					+ PAY_PayrollParamTable.STATUS_FLD + "=  ?"
					+ " WHERE " + PAY_PayrollParamTable.PAYROLLPARAMID_FLD + "= ?";

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.FROMDATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PAY_PayrollParamTable.FROMDATE_FLD].Value = objObject.FromDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.TODATE_FLD, OleDbType.Date));
				ocmdPCS.Parameters[PAY_PayrollParamTable.TODATE_FLD].Value = objObject.ToDate;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.PAIDPERHOUR_FLD, OleDbType.Decimal));
				ocmdPCS.Parameters[PAY_PayrollParamTable.PAIDPERHOUR_FLD].Value = objObject.PaidPerHour;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.STATUS_FLD, OleDbType.Boolean));
				ocmdPCS.Parameters[PAY_PayrollParamTable.STATUS_FLD].Value = objObject.Status;

				ocmdPCS.Parameters.Add(new OleDbParameter(PAY_PayrollParamTable.PAYROLLPARAMID_FLD, OleDbType.Integer));
				ocmdPCS.Parameters[PAY_PayrollParamTable.PAYROLLPARAMID_FLD].Value = objObject.PayrollParamID;


				ocmdPCS.CommandText = strSql;
				ocmdPCS.Connection.Open();
				ocmdPCS.ExecuteNonQuery();
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}

		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to get all data from PAY_PayrollParam
		///    </Description>
		///    <Inputs>
		///               
		///    </Inputs>
		///    <Outputs>
		///       DataSet
		///    </Outputs>
		///    <Returns>
		///       DataSet
		///    </Returns>
		///    <Authors>
		///       Code Generate 
		///    </Authors>
		///    <History>
		///       Friday, January 12, 2007
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public DataSet List()
		{
			const string METHOD_NAME = THIS + ".List()";
			DataSet dstPCS = new DataSet();


			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = String.Empty;

				strSql = "SELECT "
					+ PAY_PayrollParamTable.PAYROLLPARAMID_FLD + ","
					+ PAY_PayrollParamTable.FROMDATE_FLD + ","
					+ PAY_PayrollParamTable.TODATE_FLD + ","
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD + ","
					+ PAY_PayrollParamTable.STATUS_FLD
					+ " FROM " + PAY_PayrollParamTable.TABLE_NAME;
				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				ocmdPCS.Connection.Open();

				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstPCS, PAY_PayrollParamTable.TABLE_NAME);

				return dstPCS;
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}


		}


		//**************************************************************************              
		///    <Description>
		///       This method uses to update a DataSet
		///    </Description>
		///    <Inputs>
		///        DataSet       
		///    </Inputs>
		///    <Outputs>
		///       
		///    </Outputs>
		///    <Returns>
		///       
		///    </Returns>
		///    <Authors>
		///       Code Generate
		///    </Authors>
		///    <History>
		///       Friday, January 12, 2007
		///    </History>
		///    <Notes>
		///    </Notes>
		//**************************************************************************
		public void UpdateDataSet(DataSet pData)
		{
			const string METHOD_NAME = THIS + ".UpdateDataSet()";
			string strSql;
			OleDbConnection oconPCS = null;
			OleDbCommandBuilder odcbPCS;
			OleDbDataAdapter odadPCS = new OleDbDataAdapter();

			try
			{
				strSql = "SELECT "
					+ PAY_PayrollParamTable.PAYROLLPARAMID_FLD + ","
					+ PAY_PayrollParamTable.FROMDATE_FLD + ","
					+ PAY_PayrollParamTable.TODATE_FLD + ","
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD + ","
					+ PAY_PayrollParamTable.STATUS_FLD
					+ " FROM " + PAY_PayrollParamTable.TABLE_NAME;


				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				odadPCS.SelectCommand = new OleDbCommand(strSql, oconPCS);
				odcbPCS = new OleDbCommandBuilder(odadPCS);
				pData.EnforceConstraints = false;
				odadPCS.Update(pData, PAY_PayrollParamTable.TABLE_NAME);

			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}

			catch (InvalidOperationException ex)
			{
				throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}

			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}

		public decimal GetPaidPerHour()
		{
			const string METHOD_NAME = THIS + ".GetPaidPerHour()";
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			try
			{
				string strSql = "SELECT TOP 1"
					+ PAY_PayrollParamTable.PAIDPERHOUR_FLD
					+ " FROM " + PAY_PayrollParamTable.TABLE_NAME
					+ " WHERE ISNULL(" + PAY_PayrollParamTable.STATUS_FLD + ",0) = 1"
					+ " ORDER BY " + PAY_PayrollParamTable.FROMDATE_FLD + " DESC";

				
				oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);

				ocmdPCS.Connection.Open();
				object objResult = ocmdPCS.ExecuteScalar();
				try
				{
					return Convert.ToDecimal(objResult);
				}
				catch
				{
					return 0;
				}
			}
			catch (OleDbException ex)
			{
				throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex);
			}
			catch (Exception ex)
			{
				throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex);
			}
			finally
			{
				if (oconPCS != null)
				{
					if (oconPCS.State != ConnectionState.Closed)
					{
						oconPCS.Close();
					}
				}
			}
		}
	}
}